{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2a932eb3-f1f4-447e-9859-1dfa7003cb8f",
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'C:\\\\study-analyse\\\\dax_best_practice\\\\data_factory\\\\产品销售数据\\\\产品销售数据\\\\产品清单.xlsx'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_948/744911079.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     23\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mbuy_count\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     24\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 25\u001b[1;33m \u001b[0mproduct_list\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_excel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgetcwd\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;34m'\\产品销售数据\\产品清单.xlsx'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     26\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     27\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mrandom_product\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python39\\site-packages\\pandas\\util\\_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    309\u001b[0m                     \u001b[0mstacklevel\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstacklevel\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    310\u001b[0m                 )\n\u001b[1;32m--> 311\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    312\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    313\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python39\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36mread_excel\u001b[1;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)\u001b[0m\n\u001b[0;32m    362\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    363\u001b[0m         \u001b[0mshould_close\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 364\u001b[1;33m         \u001b[0mio\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    365\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m!=\u001b[0m \u001b[0mio\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    366\u001b[0m         raise ValueError(\n",
      "\u001b[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python39\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, path_or_buffer, engine, storage_options)\u001b[0m\n\u001b[0;32m   1189\u001b[0m                 \u001b[0mext\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"xls\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1190\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1191\u001b[1;33m                 ext = inspect_excel_format(\n\u001b[0m\u001b[0;32m   1192\u001b[0m                     \u001b[0mcontent_or_path\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1193\u001b[0m                 )\n",
      "\u001b[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python39\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36minspect_excel_format\u001b[1;34m(content_or_path, storage_options)\u001b[0m\n\u001b[0;32m   1068\u001b[0m         \u001b[0mcontent_or_path\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mBytesIO\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcontent_or_path\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1069\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1070\u001b[1;33m     with get_handle(\n\u001b[0m\u001b[0;32m   1071\u001b[0m         \u001b[0mcontent_or_path\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"rb\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mis_text\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1072\u001b[0m     ) as handle:\n",
      "\u001b[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python39\\site-packages\\pandas\\io\\common.py\u001b[0m in \u001b[0;36mget_handle\u001b[1;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001b[0m\n\u001b[0;32m    709\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    710\u001b[0m             \u001b[1;31m# Binary mode\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 711\u001b[1;33m             \u001b[0mhandle\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mhandle\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mioargs\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmode\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    712\u001b[0m         \u001b[0mhandles\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mhandle\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    713\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'C:\\\\study-analyse\\\\dax_best_practice\\\\data_factory\\\\产品销售数据\\\\产品销售数据\\\\产品清单.xlsx'"
     ]
    }
   ],
   "source": [
    "import random\n",
    "import datetime\n",
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "def diff_month(d1, d2):\n",
    "    return abs((d1.year - d2.year) * 12 + d1.month - d2.month)\n",
    "\n",
    "def random_date():\n",
    "    return datetime.datetime(2021, 1, 1) + datetime.timedelta(days=random.randint(0,729))\n",
    "\n",
    "def random_buy_count(product):\n",
    "    buy_count = random.randint(1, 7)\n",
    "    month_count = diff_month(datetime.datetime(2021, 1, 1), product['销售日期'])\n",
    "    buy_count = int((1 + 0.1*month_count) * buy_count)\n",
    "        \n",
    "    product_type = product['类别']\n",
    "    hot_types = ['文具', '玩具', '书籍']\n",
    "    \n",
    "    if product_type in hot_types:\n",
    "        buy_count = buy_count + random.randint(0,7)\n",
    "    \n",
    "    return buy_count\n",
    "\n",
    "product_list = pd.read_excel(os.getcwd() + '\\产品清单.xlsx') \n",
    "\n",
    "def random_product():\n",
    "    product_id = random.randint(0, 27)\n",
    "    if product_id > 9:\n",
    "        product_id = random.randint(5,9)\n",
    "    \n",
    "    x = product_list.iloc[[product_id]]\n",
    "    return x.to_dict('records')[0]\n",
    "\n",
    "shop_list = ['街心花园店','育才小学店','咔咔广场店']\n",
    "\n",
    "def random_shop():\n",
    "    r = random.randint(0, 10)\n",
    "    if r <=5:\n",
    "        return shop_list[0]\n",
    "    elif r > 5 and r < 8:\n",
    "        return shop_list[1]\n",
    "    else:\n",
    "        return shop_list[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e4bae75-9e82-49ac-9764-5eefcd5893b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "\n",
    "order_list = []\n",
    "\n",
    "# 起始日期\n",
    "start_date = datetime.datetime(2021, 1, 1)\n",
    "\n",
    "\n",
    "\n",
    "order_count = 30000\n",
    "\n",
    "for i in range(order_count):\n",
    "    product = random_product()\n",
    "    sale_date = random_date()\n",
    "    product['销售日期'] = sale_date\n",
    "    product['店铺'] = random_shop()\n",
    "    product['销售数量'] = random_buy_count(product)\n",
    "    \n",
    "    order_list.append(product)\n",
    "\n",
    "order_list_df = pd.DataFrame(order_list)\n",
    "    \n",
    "order_list_df.to_excel(os.getcwd() + '\\订单.xlsx', sheet_name='data', index=False)\n",
    "\n",
    "print('done.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18a3a88e-22dc-4e2d-a8a0-a012f70084dd",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
